/* 
 *   Classname:  datapower.ExportToCSV
 * 
 *   Date       Developer   Description
 *   Nov 28, 2017    周成功              新建
 *
 */
package demo.database.csv;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import com.titan.core.utils.data.FormatUtils;
import com.titan.core.utils.db.DBUtils;
import com.titan.core.utils.db.DataSourceUtils;
import com.titan.core.utils.db.JdbcUtils;
import com.titan.core.utils.db.RecordSet;
import com.titan.core.utils.file.CSVUtils;
import com.titan.core.utils.file.ClassUtils;
import com.titan.core.utils.file.FileUtils;

/**
 *
 *
 * @author 周成功
 *
 */
public class OracleToCSV {
    private static String rootPath  = null;
    private static String ip  = null;
    private static String dbname  = null;
    
    private static void localEnv() {
        ip  = "192.168.128.212";
        dbname = "orcl";
        rootPath = "D:\\我的项目\\项目-四通\\Workspace\\BackupData\\";
    }
    private static void serverEnv() {
        String ip  = "cch02017.harbin.ford.com";
        String dbname = "hrbsgts";
        rootPath = "F:\\export\\";
    }

    public static void main(String[] args) {
        Connection conn = null;
        try {
            localEnv();
//          serverEnv();
          
            String username = "hrbsgts";
            String password = "hrbsgts";
            String driver = "oracle.jdbc.driver.OracleDriver";
            String url = "jdbc:oracle:thin:@" + ip + ":1521:" + dbname;
          
            DataSource datasource = DataSourceUtils.registerDataSource(null, driver, url, username, password, 5, 5);
            JdbcUtils.setDataSource(datasource);
            conn = datasource.getConnection();
            String batchno = FormatUtils.getSysdate("yyyy-MM-dd HH.mm.ss");
            
            String sql = "select table_name from user_tab_comments";
            RecordSet rest = DBUtils.executeQueryToRecordSet(conn, sql);
            String[] tables = rest.getColumnString("table_name");
            for(int i=0; i<tables.length; i++) {
                run(conn, tables[i], batchno);
            }
            
//            run(conn, "BASE_GATE", batchno);
//            System.out.println(FormatUtils.toString(tables));
        } catch(Exception ex) {
            ex.printStackTrace();
        } finally {
            DBUtils.close(conn);
        }
    }
    private static void run(Connection conn, String tablename, String batchno) throws Exception {
        String sql = "select * from " + tablename;
        RecordSet rest = DBUtils.executeQueryToRecordSet(conn, sql);
        
        List<String[]> lines = new ArrayList<String[]>();
        String[] hearder = new String[rest.getColumnCount()];
        for(int i=0; i<rest.getColumnCount(); i++) {
            hearder[i] = rest.getColumnName(i+1);
        }
        lines.add(hearder);
        
        while(rest.moveNext()) {
            String[] data = new String[rest.getColumnCount()];
            for(int i=0; i<rest.getColumnCount(); i++) {
                Object object = rest.getObject(i+1);
                if(object==null) {
                    data[i] = "";
                } else {
                    String clazz = ClassUtils.getClass(object).getName();
                    if("java.sql.Timestamp".equals(clazz)) {
                        java.sql.Timestamp value = (java.sql.Timestamp)object;
                        data[i] = FormatUtils.dateToString(value, "yyyy-MM-dd HH:mm:ss");
                    } else if("java.lang.String".equals(clazz)) {
                        data[i] = FormatUtils.toString(object);
                    } else if("java.math.BigDecimal".equals(clazz)) {
                        data[i] = FormatUtils.toString(object);
                    } else {
                        data[i] = FormatUtils.toString(object);
                    }
                }
            }
            lines.add(data);
        }
        String file = rootPath + batchno + "\\" + tablename + ".csv";
        FileUtils.create(file);
        CSVUtils.write(file, lines);
    }
}
